#!/usr/bin/env python
# _*_ coding:utf-8 _*_
"""
@Version: python3.9
@Project: Excels
@File:    xlwings_ex.py
@Date:    2022/11/9 22:04
@Author:  jia
@Desc:    xlwings库的使用示例
"""

import xlwings as xw
"""
操作对象分为：APP>>books>>sheets>>range
APP:excel程序
books：工作薄
sheets: 工作表
range: 单元格
"""
'''APP'''
app = xw.App(visible=True, add_book=False)
app.display_alerts = False  # 关闭一些提示信息，可以加快运行速度。 默认为 True。
app.screen_updating = True  # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。

'''books工作簿'''
wb = app.books.add()                   # 新建工作簿。
wb2 = app.books.open(r'file_path')      # 打开现有的工作簿
wb3 = app.books.active                  # 获取当前活动的工作簿

'''sheets工作表'''
sheet = xw.Book().sheets('sheet1')         # 新增一个表格
sht1 = wb.sheets.add()                 # 新建工作表，默认新建的放在最前面。
wb.sheets.add(name=None, before=None, after=None)
sht2 = wb.sheets.add('新建工作表', after=sht1)   # 新建工作表，放在sht工作表后面。
sht1 = wb.sheets.active                 # 获取当前活动的工作表
sht3 = wb.sheets[0]                     # 按索引获取工作表
sht4 = wb.sheets['Sheet1']              # 按表名获取工作表
num = wb.sheets.count                     # 获得工作簿中工作表sheet的数量
# xw.Range ('A1:B2').count              # 返回所选范围单元格数量，此处输出4

# 自动设置工作表的宽，高
sheet.autofit(axis='c')        # 列的宽度
sheet.autofit(axis='r')        # 行的宽度

# 清除工作表
sheet.clear()                  # 清空工作表中的内容和格式
sheet.delete()                 # 删除工作表
sheet.clear_contents()         # 清除工作表的内容，但保留格式

"""
# 方法和属性
sheet.book                     # 返回指定Sheet的book,输出 <Book [2.xlsx]>
sheet.cells                    # 返回一个Range对象，该对象表示Sheet上的所有单元格（而不仅仅是当前正在使用的单元格）输出<Range [2.xlsx]Sheet1!$1:$1048576>
sheet.name                     # 获取工作表的名称
sheet.name = '我爱python知识学堂'# 工作表重命名
sheet.index                    # 返回对应sheet的索引值，从0计数
"""

# 获取连续单元格数据区域最大的行数（不计算空行之后的）
row = sht.range('A2').end('down').row
row = sht.range('A2').expand('down').rows.count
# 获取最大行数（包括中间的空行）
row_num2 = sheet.used_range.last_cell.row

# 获取连续单元格数据区域最大的列数
col_num = sheet.range('A2').end('right').column # 方法1
col = sheet.range('A2').expand('right').columns.count
# 获取最大列数（包括数据中间的空列）
col_num2 = sheet.used_range.last_cell.column  

"""
# 返回工作表中已经使用的单元格区域
sheet.api.UsedRange 
sheet.used_range  
"""

# 取值
# sheet.cells(行号,列号).value


'''Range单元格 '''
b3 = sht1.range('b3').value       # 获取 b3 中的值
sht1.range(3, 2).value = 'b3'     # 写入b3的值
b3_value = sht1.range(3, 2).value  # 也可以根据行列号读取
a1_c4_value = sht1.range('a1:c4').options(ndim=2).value  # 读取一段区间内的值 二维的数据
# a1_c4_value = sht.range((1, 1), (4, 3)).options(ndim=2).value  # 从1行1列---到4行3列，效果同上。

"""
'''方法和属性'''
xw.Range('A1').value = 12     # 设置值
xw.Range('A1').raw_value      # 直接获取并设置所使用（pywin32）引擎发送/接受的值，而无需进行任何xlwings数据清理/转换。
xw.Range ('A1:B2').value      # 引用区域并取值，输出[[1.0, 9.0],[2.0, 10.0]]，以二元list形式
xw.Range ((1,1), (2,2)).value # 取1行1列--2行2列的值
"""

# 设置单元格大小
sht1.autofit()                     # 自动调整单元格大小。注：此方法是在单元格写入内容后，再使用，才有效。
sht1.range(1, 4).column_width = 5  # 设置第4列 列宽。（1,4）为第1行第4列的单元格
sht1.range(1, 4).row_height = 20   # 设置第1行 行高
xw.Range('A1').autofit()         # 自动调整范围内所有单元格的宽度和高度。
sheet.range('A1:B2').columns.autofit()  # 如果仅自动调整列的宽度，
sheet.range('A1:B2').rows.autofit()    # 如果仅自动调整行的高度
height = xw.Range('A1:B2').height     # 返回单元格(范围)的高度
width = xw.Range('A1:B2').width      # 返回范围的宽度
hei_2 = xw.Range('B2:C4').row_height  # 获取范围的高度（以磅为单位）
xw.Range('B2:C4').row_height = 15  # 设置范围的高度（以磅为单位）

# 调整指定范围的大小
xw.Range('A1:A3').resize(row_size=None, column_size=None)

# 设置单元格颜色
color = xw.Range('A1').color         # 获取A1单元格的背景色。
xw.Range('A1').color = (255, 255, 255)  # 设置A1单元格的背景色，RGB颜色
xw.Range('A1').color = None          # 删除背景色
b3.api.Font.ColorIndex = 3  # 设置字体的颜色，具体颜色索引见下方。

# 设置单元格字体大小
b3.api.Font.Size = 24  # 设置字体的大小。
b3.api.Font.Bold = True  # 设置为粗体。

# 设置单元格格式
b3.api.HorizontalAlignment = -4108  # -4108 水平居中。 -4131 靠左，-4152 靠右。
b3.api.VerticalAlignment = -4130  # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
b3.api.NumberFormat = "0.00"  # 设置单元格的数字格式。
         
# 清除单元格
xw.Range('A1').clear()          # 清除所选择单元格的内容和格式，可选择范围
xw.Range('A1').clear_contents() # 清除范围的内容，但保留格式。

# 设置边框
# Borders(9) 底部边框，LineStyle = 1 直线。
b3.api.Borders(9).LineStyle = 1
b3.api.Borders(9).Weight = 3  # 设置边框粗细。

# Borders(7) 左边框，LineStyle = 2 虚线。
b3.api.Borders(7).LineStyle = 2
b3.api.Borders(7).Weight = 3

# Borders(8) 顶部框，LineStyle = 5 双点划线。
b3.api.Borders(8).LineStyle = 5
b3.api.Borders(8).Weight = 3

# Borders(10) 右边框，LineStyle = 4 点划线。
b3.api.Borders(10).LineStyle = 4
b3.api.Borders(10).Weight = 3

# Borders(5) 单元格内从左上角 到 右下角。
b3.api.Borders(5).LineStyle = 1
b3.api.Borders(5).Weight = 3

# Borders(6) 单元格内从左下角 到 右上角。
b3.api.Borders(6).LineStyle = 1
b3.api.Borders(6).Weight = 3

# 如果是一个区域的单元格，内部边框设置如下
# # Borders(11) 内部垂直边线。
# b3.api.Borders(11).LineStyle = 1
# b3.api.Borders(11).Weight = 3
#
# # Borders(12) 内部水平边线。
# b3.api.Borders(12).LineStyle = 1
# b3.api.Borders(12).Weight = 3

# 合并拆分单元格
sht1.range('C8:D8').api.merge()  # 合并单元格 C8 到 D8
sht1.range('C8:D8').api.unmerge()  # 拆分单元格。

# 插入 、删除 一行
sht1.range('a3').api.EntireRow.Delete()  # 会删除 ’a3‘ 单元格所在的行。
sht1.api.Rows(3).Insert()  # 会在第3行插入一行，原来的第3行下移。
sheet.api.row('2:4').insert    # 插入行，在第2-4行插入空白行
sheet.api.row('2:4').delete    # 删除行

# 插入单元格
xw.Range('A3').insert(shift='right')

# 插入 、删除 一列
sht1.range('c2').api.EntireColumn.Delete()  # 会删除 ’c2‘ 单元格所在的列。
sht1.api.Columns(3).Insert()  # 会在第3列插入一列，原来的第3列右移。(也可以用列的字母表示)

# 获取最大行数，和列数
cell = sht1.used_range.last_cell
rows = cell.row
columns = cell.column

# 返回指定范围的单元格行、列数量
range2 = xw.Range('A1:C4').last_cell
row2 = range2.last_cell.row            # 4
col2 = range2.last_cell.column         # 3

# 获取最大行数
# cell = sht1.range("a1").expand("down")
# max_rows = cell.rows.count  

# 插入、读取公式
sht1.range('d1').formula = '=sum(e1+f1)'  # 插入公式
print(sht1.range('d1').formula)          

"""
# 添加超链接
xw.Range ('A9').add_hyperlink(address='www.baidu.com')

xw.Range ('A10').address      # 返回表示范围参考的字符串值，输出 $A$10
xw.Range ('A1').api           # 返回所使用引擎的本机对象
xw.Range ('B1:C4').column     # 返回所选范围第一列的列标，此处输出2
xw.Range ('B2:C4').row        # 返回所选范围第一行的行标，此处输出2
"""

# 此属性返回一个Range对象，该对象表示由（但不包括）空白行和空白列或工作表的边缘的任意组合限制的范围，好比是一片连接的区域
# xw.Range ('A1').current_region

# 删除单元格A1,有参数left和up，如delete('up')。如果省略，Excel将根据范围的形状进行决定。
xw.Range('A1').delete()

# 返回一个Range对象，该对象表示包含源范围的区域末尾的单元格。此处输出<Range [3.xlsx]Sheet1!$A$8>，
# 参数可传down,up,left,right，其实也是返回ctrl + 方向
xw.Range('A1').end('down')

# 以指定的格式返回范围的地址
xw.Range('A1:B2').get_address()
# 参数：
# row_absolute(bool ,默认为True)–设置为True可以将引用的行部分作为绝对引用返回。
# #column_absolute(bool,默认为True)–设置为True可以将引用的列部分作为绝对引用返回。
# include_sheetname(bool ,默认为False)–设置为True可以在地址中包含工作表名称。
# external(bool ,默认为False)–设置为True以返回带有工作簿和工作表名称的外部引用。
# 具体的情况大家可以传入几个参数试试


# 获取范围行和范围列
row3 = xw.Range('B2:C4').rows        # 返回一个RangeRows对象，该对象表示指定范围内的行。
col3 = xw.Range('B2:C4').columns     # 返回一个RangeRows对象，该对象表示指定范围内的列。
count = xw.Range('B2:C4').rows.count   # 获取范围行

"""
# 获取范围列
xw.Range('B2:C4').columns.count
xw.Range ('B2').left           # 返回从A列的左边缘到范围左边缘的距离
xw.Range ('B2').top            # 返回从第1行的顶部边缘到范围的顶部边缘的距离
xw.Range ('A1').hyperlink      # 返回单元格中的超链接（对多个单元格没效）
"""

form = xw.Range('A1').number_format   # 获取设置Range的number_format
# 设置Range的number_format
xw.Range('A1:C3').number_format = '0.00%'
xw.Range('A1:A3').paste()      # 将剪贴板中的范围粘贴到指定范围

"""
# 选定单元格进行移动
xw.Range('B2:C4').offset(row_offset=0,column_offset=0)
#row_offset行偏移，column_offset列偏移

xw.Range('B2:C4').shape        # 以数组的形式返回所选范围的值
xw.Range('B2:C4').sheet        # 返回Range所属的Sheet对象
xw.Range('B2:C4').size         # 返回所选范围单元格个数(元素个数)
"""

'''排序，删除重复值'''
# 排序使用方法：
# 1、选择需要排序的区域。这里用 'a2' 是因为排序的数据送从第二行开始的，第一行是标题，不应该参与排序。
# 2、选择按那一列进行排序 Key1=sht.range('c2').api， 这里选择的是按 第 C 列排序，所以这里选择 c1 和 c2 都可以。
# 3、Order1=1 为升序，2为降序。
sht1.range('a2', (rows, columns)).api.Sort(Key1=sht1.range('c2').api, Order1=1)

# 删除重复值使用方法：
# RemoveDuplicates(3) 为按第3列内容进行删除重复项。
sht1.range('a2', (rows, columns)).api.RemoveDuplicates(3)

'''同个表格复制、粘贴'''
# 复制 a2 到 a6 之间单元格的值，粘贴到'a15'中
sht1.range('a2', 'a6').api.Copy(sht1.range('a15').api)

'''跨表格复制、粘贴'''
my_values = sht1.range('a2：d4').options(ndim=2).value  # 读取二维的数据
sht1.range('a1').value = my_values

# 选取第一列
rng=sht1. range('A1').expand('down')
rng.value=['a1','a2','a3']

# 选取第一行
rng=sht1.range('A1').expand('right')
rng2=['a1','b1']

# 将a1,a2,a3输入第一列，b1,b2,b3输入第二列
list1=[['a1','a2','a3'], ['b1','b2','b3']]
sht1.range('A1').value = list1

# 选取表格
rng.sht.range('A1').expand('table')
rng.value=[['a1','a2','a3'],['b1','b2','b3']]

# 保存工作薄 并 退出程序
# wb.save(r'file_path')
# wb.close()
# app.quit()
app.kill()






